package com.mmy.dao.visa;

import java.util.List;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import com.mmy.domain.visa.VVisaBase;

public interface VVisaBaseRepository extends JpaRepository<VVisaBase, Integer> {

	/*
	 * 根据id查找
	 */
	@Query(value = "select *  from vVisaBase where iVisaID = :iVisaID", nativeQuery = true)
	VVisaBase findByVisaID(@Param("iVisaID") int iVisaID);

//按照搜索条件搜索
	@Query(value = "select *  from vVisaBase where ( cVisaName like %:cVisaName%) and (:iZoneID = \"\" or iZoneID = :iZoneID)  and (:iType = \"\" or bType = :iType) ", nativeQuery = true)
	List<VVisaBase> findBySearch(@Param("cVisaName") String strVisaName, @Param("iZoneID") String strZoneID,
			@Param("iType") String strType);

	/**
	 * 前台首页签证模块 查询最新创建的签证前8条
	 *
	 * @author 创建人 hanxl
	 * @date 创建日期 2021.02.23
	 */
	@Query(value = "select *  from vVisaBase order by dCreateDate desc limit 8 ", nativeQuery = true)
	List<VVisaBase> findVisa();

	/**
	 * 后台签证管理中显示自己公司的签证信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vVisaBase where cSpare1=:iOrgID ", nativeQuery = true)
	List<VVisaBase> findByOrgID(int iOrgID);

	/**
	 * 后台签证管理中超级管理员登录显示所有签证信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vVisaBase where (:strVisaName = \"\" or cVisaName like %:strVisaName%) and (:strZoneID = \"\" or iZoneID = :strZoneID) and (:strType = \"\" or bType = :strType)  limit :startIndex,:count", nativeQuery = true)
	List<VVisaBase> findSearchALL(@Param("strVisaName") String strVisaName, @Param("strZoneID") String strZoneID,
			@Param("strType") String strType, @Param("startIndex") int startIndex, @Param("count") int count);

	/**
	 * 后台签证管理中显示自己公司的签证信息
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select * from vVisaBase where (:strVisaName = \"\" or cVisaName like %:strVisaName%) and (:strZoneID = \"\" or iZoneID = :strZoneID) and (:strType = \"\" or bType = :strType) "
			+ "and cSpare1=:iOrgID  limit :startIndex,:count ", nativeQuery = true)
	List<VVisaBase> findSearchByOrgID(@Param("iOrgID") int iOrgID, @Param("strVisaName") String strVisaName,
			@Param("strZoneID") String strZoneID, @Param("strType") String strType, @Param("startIndex") int startIndex,
			@Param("count") int count);

	/**
	 * 后台签证管理中超级管理员登录显示所有公司的签证信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select count(*)  from vVisaBase where(:strVisaName = \"\" or cVisaName like %:strVisaName%) and (:strZoneID = \"\" or iZoneID = :strZoneID) and (:strType = \"\" or bType = :strType)", nativeQuery = true)
	int countALL(@Param("strVisaName") String strVisaName, @Param("strZoneID") String strZoneID,
			@Param("strType") String strType);

	/**
	 * 后台签证管理中显示自己公司的签证信息 获取分页
	 *
	 * @author 创建人 hanxl 整理 修改人hanxl
	 * @date 创建日期 2020.11.19 修改时间 2021.03.17
	 */
	@Query(value = "select count(*)  from vVisaBase where  (:strVisaName = \"\" or cVisaName like %:strVisaName%) and (:strZoneID = \"\" or iZoneID = :strZoneID) and (:strType = \"\" or bType = :strType) and cSpare1=:iOrgID", nativeQuery = true)
	int countByOrgID(@Param("iOrgID") int iOrgID, @Param("strVisaName") String strVisaName,
			@Param("strZoneID") String strZoneID, @Param("strType") String strType);

}
